import datetime
import logging

from django.db import connection
from django.db.models import Avg, Sum, Q, F
from django.http import HttpResponse
from django.shortcuts import render, redirect, get_object_or_404

# 处理业务逻辑,第一个参数必须是request
from django.urls import reverse

from user import models as userModels
from user.models import Order, Role

logger = logging.getLogger('django')


def index(request, **kwargs):
    if kwargs["hou"]:
        print(kwargs["hou"])
    #获取传统? 参数
    name = request.GET.get("name")
    # 必须使用HttpResponse类包装
    return HttpResponse("欢迎进入Django项目!! %s"%name)

# 顺序必须和url中匹配一致
def userinfo(request, year, month):
    #return HttpResponse("用户: %s 年 %s 月"%(year, month))
    print(reverse('hou'))  # 获取name为hou的url
    return redirect(reverse('hou'))  # 重定向


# 使用分组匹配,可以不按顺序,但key名字必须与路径中一致
def userlist(request, v2, v1):
    return HttpResponse("用户列表: %s 年 %s 月" % (v2, v1))


# 普通添加
def adduser(request):
    userModels.User.objects.create(name="侯征", age=25)
    return HttpResponse("添加成功")
# 一对多添加
def addorder(request):
    # 需要添加外键user_id
    user = userModels.User.objects.get(id=1)
    userModels.Order.objects.create(name="侯征的订单01", user_id=1)  # 直接指定user_id
    userModels.Order.objects.create(name="侯征的订单02", user=user)  # 指定关联的user
    # new实例添加
    order01 = Order(name="侯征的订单01", user_id=1)
    order01.save()
    return HttpResponse("添加成功")
# 多对多添加
def addrole(request):
    user01 = userModels.User.objects.get(id=1)
    user02 = userModels.User.objects.get(id=2)
    # userModels.Role.objects.create(name="java开发")
    role = Role(name="python开发")
    role.save()  # 必须先保存
    # 添加多对多关系
    role.users.add(user01)
    role.users.add(user02)
    role.save()
    return HttpResponse("添加成功")


# 更新
def updateuser(request):
    # 直接更新,但是无法更新auto_now属性的字段
    userModels.User.objects.filter(id=2).update(**{"name": "菜菜"})
    userModels.User.objects.filter(id=2).update(name="哈哈", age=26)
    user = userModels.User.objects.get(id=2)
    user.__dict__.update({"age": 28})  # 字典更新
    user.name = "菜菜"   # 属性更新
    user.save()
    return HttpResponse("更新成功")
# 一对多更新
def updateorder(request):
    # 直接更新,直接修改关联外键属性
    userModels.Order.objects.filter(id=1).update(**{"name": "菜菜的订单", "user_id": 2})
    order = userModels.Order.objects.get(id=1)
    # 直接赋值实例,修改关联外键
    order.user = userModels.User.objects.get(id=2)
    # order.__dict__ = {"user":userModels.User.objects.get(id=2)}
    order.save()  # 必须save才会更新
    return HttpResponse("更新成功")

# 多对多更新
def updaterole(request):
    # 只能间接更新
    user01 = userModels.User.objects.get(id=1)
    user02 = userModels.User.objects.get(id=2)
    role = userModels.Role.objects.get(id=1)
    role.users.remove(user02)  # 移除某一个关联,删除数据
    role.users.clear()  # 清楚所有关联,删除关联表数据
    role.users.add(user01)  # 添加关联
    role.name = "大数据开发"
    role.save()
    return HttpResponse("更新成功")


# 删除
def deleteuser(request):
    # 删除所有必须加all(),否则会报错
    # userModels.User.objects.all().delete()
    userModels.User.objects.filter(name="侯征").delete()
    userModels.User.objects.get(id=2).delete()
    return HttpResponse("删除成功")

# 查询
def selectuser(request):
    # 普通查询
    userModels.User.objects.get(id=1)  # 获取单个,不存在报错
    count = userModels.User.objects.filter(name="侯征").count()
    userModels.User.objects.exclude(name="侯征")  # 非查询
    # 返回QuerySet对象可以迭代
    userModels.User.objects.exclude(name__regex=r'^hou')
    # 大于小于 in 查询
    userModels.User.objects.filter(id__gt=1, id__lte=10)  # 获取id大于1 且小于等于10的
    userModels.User.objects.filter(id__in=[1, 2, 3, 4])  # in 查询
    userModels.User.objects.exclude(id__in=[1, 2, 3, 4])  # not in 查询
    userModels.User.objects.filter(name__isnull=True)  # name isnull
    # 包含  like
    userModels.User.objects.filter(name__contains="hou")
    userModels.User.objects.filter(name__icontains="HOU")  # 不区分大小写
    # 开头结尾  startswith，istartswith, endswith, iendswith(不区分大小写)
    userModels.User.objects.filter(name__startswith="hou")  # 不区分大小写
    # 范围
    userModels.User.objects.filter(id__range=[1, 3])
    # 排序 默认正序
    userModels.User.objects.filter(id__range=[1, 3]).order_by('id')
    # -age 倒序
    userModels.User.objects.filter(id__range=[1, 3]).order_by('-age', 'id')
    # limit
    userModels.User.objects.all()[2:20]  # 左开右闭
    userModels.User.objects.all()[:20]   # 前20个  [20:] 第20个之后的
    # 聚合函数:返回字典   annotate(会按主表的主键分组) aggregate(单纯对所有数据聚合)
    aggregate = userModels.User.objects.aggregate(avg_age=Avg('age'))
    # 返回分组结果query_set对象,多一个sum属性
    userModels.User.objects.annotate(sum=Sum('age'))
    # values_list  返回元组,可选择返回字段
    userModels.User.objects.values_list('id', 'name')  # 每一条数据返回只有两列数据的元组
    # 单列可以使用flat参数 ,为True表示返回一直值,而不是元组
    userModels.User.objects.values_list('name', flat=True)
    # values()  返回字典,而不是对象
    userModels.User.objects.values("name").distinct()  # 去重
    # 时间字段
    userModels.User.objects.filter(writetime__day=1)  # 查询一号的
    userModels.User.objects.filter(writetime__gte=datetime.date(2019, 2, 21))
    userModels.User.objects.filter(writetime__year=2019)
    userModels.User.objects.filter(writetime__week_day=6)  # 周五,按照的是0-6这种
    userModels.User.objects.filter(writetime__month__gte=1)  # 大于等于一月
    userModels.User.objects.filter(writetime__hour__gt=9)  # 大于九点


    # Q 逻辑组合查询: 取名字包含 hou 并且 不是以g结尾,并且年龄是24大于等于24或者小于等于22的
    userModels.User.objects.filter(Q(name__contains='hou') & ~Q(name__endswith='g'), Q(age__gte=24) | Q(age__lte=22) )


    # F  获取某个字段值,与其他字段进行运算,比较
    # 模型字段之间比较需要F
    userModels.User.objects.filter(id__lte=F('age')-5)  # F 支持算术
    userModels.User.objects.filter().update(age=F('age')-5)  # 将所有用户年龄+5
    # 日期可以和 timedelta 计算, 所有日期+1天
    userModels.User.objects.filter().update(writetime=F('writetime') + datetime.timedelta(days=1))



    # 外键查询
    # 一对多查询(反向查询,查询用户的所有订单)
    user = userModels.User.objects.get(id=1)
    # model中有related_name,用这个,没有就是model_set
    for o in user.order_list.all():
        print(o.name)
    # 一对多(速度快)
    user__all = userModels.Order.objects.filter(user=user).all()
    for u in user__all:
        print(u.name)
    order = userModels.Order.objects.get(id=1)
    # 正向查询(查询订单的所属用户,只有一个)
    print(order.user.name)
    # 多对多查询
    # 反向查询
    roles = user.role_set.all()
    for roles in roles:
        print(roles.name)
    role01 = userModels.Role.objects.get(id=1)
    # 正向
    role01.users.all()

    # 其他方法
    # 找不到数据抛出404
    get_object_or_404(userModels.User, id=2)
    # 不存在就创建,返回查询或创建的对象  以及  是否创建boolean值
    #object, created = userModels.User.objects.get_or_create(name="姚振",age=22)


    # 执行原生sql
    with connection.cursor() as cursor:
        cursor.execute('select * from user where id=2')
        user002 = cursor.fetchall()  # 返回元组




    return HttpResponse("查询成功")

def selectorder(request):
    s = "hello"
    l = [111, 222, 333]  # 列表
    dic = {"name": "yuan", "age": 18}  # 字典
    date = datetime.date(1993, 5, 2)  # 日期对象
    class Person(object):
        def __init__(self, name):
            self.name = name
    person_yuan = Person("yuan")  # 自定义类对象
    person_egon = Person("egon")
    person_alex = Person("alex")
    person_list = [person_yuan, person_egon, person_alex]
    list = {"s": s, "l": l, "dic": dic, "date": date, "person_list": person_list}
    # 返回list数据到模板html页面
    return render(request, 'user/index.html', list)

def selectrole(request):
    str = "houzheng"
    html = "<a href="">点击</a>"
    name = ""
    date = datetime.datetime.now()
    list = {'str': str, 'html': html, 'name': name, "date": date}
    return render(request, 'user/filter.html', list)

def tags(request):
    s = "哈哈哈"
    l = []  # 列表
    num = 60
    class Person(object):
        def __init__(self, name):
            self.name = name
    person_yuan = Person("yuan")  # 自定义类对象
    person_egon = Person("egon")
    person_alex = Person("alex")
    person_list = [person_yuan, person_egon, person_alex]
    list = {"num": num, "s": s, "l": l, "person_list": person_list}
    return render(request, 'user/tags.html',list)

def mytag(request):
    num01 = 10
    num02 = 100
    num03 = 100
    list = {"n1": num01, "n2": num02, "n3": num03}
    return render(request, 'user/mytag.html', list)

def child(request):
    return render(request,'user/child.html')


